﻿// --------------------------------------------------------------------------------------------------------------------
// <copyright file="SQLHelper.cs" company="Collaboris Ltd.">
//   Copyright (c) Collaboris Ltd. All rights Reserved.
// </copyright>
// <summary>
//   Defines the SQLHelper type.
// </summary>
// --------------------------------------------------------------------------------------------------------------------
namespace Collaboris.Utils
{
    using System;
    using System.Data.SqlClient;
    using System.IO;
    using System.Text;

    public class SQLHelper
    {
        /// <summary>
        /// Creates the database.
        /// </summary>
        /// <param name="serverName">Name of the server.</param>
        /// <param name="databaseName">Name of the database.</param>
        /// <param name="connectionString">The connection string.</param>
        public static void CreateDatabase(string serverName, string databaseName, string connectionString)
        {
            ValidationHelper.VerifyStringArgument(connectionString, "connectionString");
            ValidationHelper.VerifyStringArgument(databaseName, "databaseName");
            ValidationHelper.VerifyStringArgument(serverName, "serverName");

            if (databaseName.Contains(" "))
                throw new ArgumentException("Database name cannot contain spaces", databaseName);

            string commandText = "CREATE DATABASE " + databaseName;
            
            SqlConnection connection = null;

            try
            {
                connection = new SqlConnection(connectionString);

                SqlCommand command = new SqlCommand(commandText, connection);

                connection.Open();
                command.ExecuteNonQuery();
            }
            finally
            {
                // Close out the connection
                if (connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
        }

        /// <summary>
        /// Runs a SQL Command from a Script File.
        /// </summary>
        /// <param name="scriptPath">The script path.</param>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="timeout">The timeout.</param>
        public static void RunSQLFromFile(string scriptPath, string connectionString, int timeout)
        {
            ValidationHelper.VerifyStringArgument(connectionString, "connectionString");
            ValidationHelper.VerifyStringArgument(scriptPath, "scriptPath");

            if (!File.Exists(scriptPath))
            {
                throw  new FileNotFoundException("Cannot find this SQL Script File '{0}'", scriptPath);
            }

            SqlConnection conn = null;
            try
            {
                using (StreamReader sr = new StreamReader(new FileStream(scriptPath, FileMode.Open, FileAccess.Read)))
                {
                    //this.Response.Write("Connecting to SQL Server database...<BR>");

                    // Create new connection to database
                    conn = new SqlConnection(connectionString);
                    conn.Open();

                    RunSQL(conn, sr, timeout);
                }
            }
            finally
            {
                // Close out the connection
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }

        /// <summary>
        /// Runs a SQL Command from a Script File.
        /// </summary>
        /// <param name="sqlText">The SQL text.</param>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="timeout">The timeout.</param>
        public static void RunSQLFromText(string sqlText, string connectionString, int timeout)
        {
            ValidationHelper.VerifyStringArgument(connectionString, "connectionString");
            ValidationHelper.VerifyStringArgument(sqlText, "scriptPath");
            SqlConnection conn = null;
            try
            {
                MemoryStream mstr = StringHelper.ConvertStringToMemoryStream(sqlText);

                using (StreamReader sr = new StreamReader(mstr))
                {
                    // Create new connection to database
                    conn = new SqlConnection(connectionString);
                    conn.Open();
                    
                    RunSQL(conn, sr, timeout);
                }
            }
            finally
            {
                // Close out the connection
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }

        /// <summary>
        /// Runs the SQL.
        /// </summary>
        /// <param name="conn">The conn.</param>
        /// <param name="sr">The sr.</param>
        /// <param name="timeout">The timeout.</param>
        private static void RunSQL(SqlConnection conn, StreamReader sr, int timeout)
        {
            while (!sr.EndOfStream)
            {
                StringBuilder sb = new StringBuilder();
                SqlCommand cmd = conn.CreateCommand();

                while (!sr.EndOfStream)
                {
                    string s = sr.ReadLine();
                    if (s != null && s.ToUpper().Trim().Equals("GO"))
                    {
                        break;
                    }

                    sb.AppendLine(s);
                }

                // Execute T-SQL against the target database
                cmd.CommandText = sb.ToString();
                cmd.CommandTimeout = timeout;

                cmd.ExecuteNonQuery();
            }
        }
    }
}
